Check that a table is clustered

If you think Oracle should be using hash cluster access to resolve your query and it is not doing so, then make sure the table is really hash clustered:

SELECT CLUSTER NAME
FROM USER_TABLES
WHERE TABLE_NAME = 'MY_TABLE'

One of three things will be returned:

• The SQL returns No rows returned. MY_TABLE may not be a table, it may be a VIEW, or a SYNONYM over another schema's table. Check USER_VIEWS and USER_SYNONYMS to find out. If it is a view, then the SQL of the view may be too complex for your WHERE clause to be merged. If it is a synonym, try the following query instead:

  SELECT CLUSTER NAME
  FROM DBA_TABLES
  WHERE OWNER = 'SCHEMA_NAME'
    AND TABLE_NAME = 'MY_TABLE'

• The SQL returns a single NULL value. This means the table is not clustered. To cluster an unclustered table, you will need the DBA to rebuild it. If the table is not supposed to be clustered after all, go back and check the indexes again.
• The SQL returns the name of a cluster - the table is clustered. Go back and check for other problems.